
--new locale resources
declare @resources xml
--a resource will be delete if its value is empty
set @resources='
<Language>
  <LocaleResource Name="Admin.Catalog.Stores.Branches">
    <Value>Branches</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Added">
    <Value>The new branch has been added successfully.</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.AddNew">
    <Value>Add new Branch</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Updated">
    <Value>The branch has been updated successfully.</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.BackToStore">
    <Value>back to store</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.EditBranch">
    <Value>Edit branch</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.AddBranch">
    <Value>Add new branch</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.AddNew">
    <Value>Add new</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Name">
    <Value>Name</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Name.Hint">
    <Value>Enter branch namr</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Name.Required">
    <Value>Name is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Website">
    <Value>Website</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Website.Hint">
    <Value>Enter branch website</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.website.Required">
    <Value>Website is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.ViewOnGoogleMap">
    <Value>View Branch address on Google map</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Email">
    <Value>Email</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Email.Hint">
    <Value>Enter email address</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Email.Required">
    <Value>Email is required.</Value>
  </LocaleResource>
 <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Country">
    <Value>Country</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Country.Hint">
    <Value>Select a country</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Country.Required">
    <Value>Country is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.StateProvince">
    <Value>State / province</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.StateProvince.Hint">
    <Value>Select State / province</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.StateProvince.Required">
    <Value>State / province is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.City">
    <Value>City</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.City.Hint">
    <Value>Enter City</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.City.Required">
    <Value>City is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Address1">
    <Value>Address</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Address1.Hint">
    <Value>Enter Address</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Address1.Required">
    <Value>Address is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Address2">
    <Value>Address 2</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Adddress2.Hint">
    <Value>Enter Address 2</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.Address2.Required">
    <Value>Address 2 is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.ZipPostalCode">
    <Value>Zip / postal code</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.ZipPostalCode.Hint">
    <Value>Enter Zip / postal code</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.ZipPostalCode.Required">
    <Value>Zip / postal code is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.PhoneNumber">
    <Value>Phone number</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.PhoneNumber.Hint">
    <Value>Enter Phone number</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.PhoneNumber.Required">
    <Value>Phone number is required.</Value>
  </LocaleResource>
   <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.FaxNumber">
    <Value>Fax number</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.FaxNumber.Hint">
    <Value>Enter Fax number</Value>
  </LocaleResource>
  <LocaleResource Name="Admin.Catalog.Stores.Branches.Fields.FaxNumber.Required">
    <Value>Fax number is required.</Value>
  </LocaleResource>

  <LocaleResource Name="Stores.Branches">
    <Value>Branches</Value>
  </LocaleResource>
  <LocaleResource Name="Stores.Branches.Phone">
    <Value>Phone</Value>
  </LocaleResource>
  <LocaleResource Name="Stores.Branches.Fax">
    <Value>Fax</Value>
  </LocaleResource>
  <LocaleResource Name="Stores.Branches.ViewOnGoogleMaps">
    <Value>View address on Google Maps</Value>
  </LocaleResource>
  </Language>
'

CREATE TABLE #LocaleStringResourceTmp
	(
		[ResourceName] [nvarchar](200) NOT NULL,
		[ResourceValue] [nvarchar](max) NOT NULL
	)

INSERT INTO #LocaleStringResourceTmp (ResourceName, ResourceValue)
SELECT	nref.value('@Name', 'nvarchar(200)'), nref.value('Value[1]', 'nvarchar(MAX)')
FROM	@resources.nodes('//Language/LocaleResource') AS R(nref)

--do it for each existing language
DECLARE @ExistingLanguageID int
DECLARE cur_existinglanguage CURSOR FOR
SELECT [ID]
FROM [Language]
OPEN cur_existinglanguage
FETCH NEXT FROM cur_existinglanguage INTO @ExistingLanguageID
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @ResourceName nvarchar(200)
	DECLARE @ResourceValue nvarchar(MAX)
	DECLARE cur_localeresource CURSOR FOR
	SELECT ResourceName, ResourceValue
	FROM #LocaleStringResourceTmp
	OPEN cur_localeresource
	FETCH NEXT FROM cur_localeresource INTO @ResourceName, @ResourceValue
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF (EXISTS (SELECT 1 FROM [LocaleStringResource] WHERE LanguageID=@ExistingLanguageID AND ResourceName=@ResourceName))
		BEGIN
			UPDATE [LocaleStringResource]
			SET [ResourceValue]=@ResourceValue
			WHERE LanguageID=@ExistingLanguageID AND ResourceName=@ResourceName
		END
		ELSE 
		BEGIN
			INSERT INTO [LocaleStringResource]
			(
				[LanguageId],
				[ResourceName],
				[ResourceValue]
			)
			VALUES
			(
				@ExistingLanguageID,
				@ResourceName,
				@ResourceValue
			)
		END
		
		IF (@ResourceValue is null or @ResourceValue = '')
		BEGIN
			DELETE [LocaleStringResource]
			WHERE LanguageID=@ExistingLanguageID AND ResourceName=@ResourceName
		END
		
		FETCH NEXT FROM cur_localeresource INTO @ResourceName, @ResourceValue
	END
	CLOSE cur_localeresource
	DEALLOCATE cur_localeresource


	--fetch next language identifier
	FETCH NEXT FROM cur_existinglanguage INTO @ExistingLanguageID
END
CLOSE cur_existinglanguage
DEALLOCATE cur_existinglanguage

DROP TABLE #LocaleStringResourceTmp
GO


-- create branch table
IF OBJECT_ID('Branch') IS NULL
BEGIN

CREATE TABLE [dbo].[Branch](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Website] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[CountryId] [int] NULL,
	[StateProvinceId] [int] NULL,
	[City] [nvarchar](max) NULL,
	[Address1] [nvarchar](max) NULL,
	[Address2] [nvarchar](max) NULL,
	[ZipPostalCode] [nvarchar](max) NULL,
	[PhoneNumber] [nvarchar](max) NULL,
	[FaxNumber] [nvarchar](max) NULL,
	[CreatedOnUtc] [datetime] NOT NULL,
	[DisplayOrder] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
IF OBJECT_ID('Branch_Country') IS NULL
BEGIN
ALTER TABLE [dbo].[Branch]  WITH CHECK ADD  CONSTRAINT [Branch_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([Id])
END
GO

IF OBJECT_ID('Branch_Country') IS NULL
BEGIN
ALTER TABLE [dbo].[Branch] CHECK CONSTRAINT [Branch_Country]
END
GO

IF OBJECT_ID('Branch_StateProvince') IS NULL
BEGIN
ALTER TABLE [dbo].[Branch]  WITH CHECK ADD  CONSTRAINT [Branch_StateProvince] FOREIGN KEY([StateProvinceId])
REFERENCES [dbo].[StateProvince] ([Id])
END
GO

IF OBJECT_ID('Branch_StateProvince') IS NULL 
BEGIN
ALTER TABLE [dbo].[Branch] CHECK CONSTRAINT [Branch_StateProvince]
END

--create store branches table

IF OBJECT_ID('StoreBranches') IS NULL
BEGIN
CREATE TABLE [dbo].[StoreBranches](
	[Store_Id] [int] NOT NULL,
	[Branch_Id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Store_Id] ASC,
	[Branch_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF OBJECT_ID('Store_Branches_Source') IS NULL
BEGIN
ALTER TABLE [dbo].[StoreBranches]  WITH CHECK ADD  CONSTRAINT [Store_Branches_Source] FOREIGN KEY([Store_Id])
REFERENCES [dbo].[Store] ([Id])
ON DELETE CASCADE
END
GO

IF OBJECT_ID('Store_Branches_Source') IS NULL
BEGIN
ALTER TABLE [dbo].[StoreBranches] CHECK CONSTRAINT [Store_Branches_Source]
END
GO

IF OBJECT_ID('Store_Branches_Target') IS NULL
BEGIN
ALTER TABLE [dbo].[StoreBranches]  WITH CHECK ADD  CONSTRAINT [Store_Branches_Target] FOREIGN KEY([Branch_Id])
REFERENCES [dbo].[Branch] ([Id])
ON DELETE CASCADE
END
GO

IF OBJECT_ID('Store_Branches_Target') IS NULL
BEGIN
ALTER TABLE [dbo].[StoreBranches] CHECK CONSTRAINT [Store_Branches_Target]
END
GO





